﻿using Awsc.DataAcquisitionSys.Core.Common;
using Microsoft.Data.Sqlite;
using System.Data;
using System.Reflection;
using System.Threading.Tasks;
using System.Xml.Linq;


namespace Awsc.DataAcquisitionSys.Core.Databases
{
    public class SQLiteHelper : IDisposable
    {
        public readonly SqliteConnection _connection;

        public SQLiteHelper()
        {
            var connectionString = @$"Data Source=Missions\MissionProfile.db;";
            _connection = new SqliteConnection(connectionString);
            _connection.Open();
        }

        public async Task<int> CreateTableAsync(string sql)
        {
            return await ExecuteNonQueryAsync(sql);
        }
        // 执行非查询SQL
        public async Task<int> ExecuteNonQueryAsync(string sql, Dictionary<string, object> parameters = null)
        {

            using var command = _connection.CreateCommand();
            command.CommandText = sql;
            AddParameters(command, parameters);
            return await command.ExecuteNonQueryAsync();

        }

        public int ExecuteNonQuery(string sql, Dictionary<string, object> parameters = null)
        {

            using var command = _connection.CreateCommand();
            command.CommandText = sql;
            AddParameters(command, parameters);
            return  command.ExecuteNonQuery();

        }

        // 通用查询方法
        public async Task<List<T>> QueryAsync<T>(string sql, Dictionary<string, object> parameters = null) where T : new()
        {

            using var command = _connection.CreateCommand();
            command.CommandText = sql;
            AddParameters(command, parameters);

            using var reader = await command.ExecuteReaderAsync();
            return MapReaderToObjects<T>(reader);

        }
        public async Task<List<Dictionary<string, object>>> QueryAsync(string sql, Dictionary<string, object> parameters = null)
        {

            using var command = _connection.CreateCommand();
            command.CommandText = sql;
            AddParameters(command, parameters);

            using var reader = await command.ExecuteReaderAsync();
            return MapReaderToDictionaryList(reader);

        }

        // 通用查询方法
        public async Task<T> FindAsync<T>(string sql, Dictionary<string, object> parameters = null) where T : new()
        {

            using var command = _connection.CreateCommand();
            command.CommandText = sql;
            AddParameters(command, parameters);

            using var reader = await command.ExecuteReaderAsync();
            return MapReaderToObject<T>(reader);

        }
        public async Task<Dictionary<string, object>> FindAsync(string sql, Dictionary<string, object> parameters = null)
        {

            using var command = _connection.CreateCommand();
            command.CommandText = sql;
            AddParameters(command, parameters);

            using var reader = await command.ExecuteReaderAsync();
            return MapReaderToDictionary(reader);

        }

        // 插入单个对象
        public async Task<int> InsertAsync<T>(T item)
        {
            ArgumentAssert.NotNull(item);

            var type = typeof(T);
            var (sql, parameters) = GenerateInsertInfo(type);
            var command = _connection.CreateCommand();
            command.CommandText = sql;
            //AddParametersToCommand(command, parameters);
            UpdateCommandParameters(command, item, parameters);
            return await command.ExecuteNonQueryAsync();

        }

        // 批量插入（自动分批）
        public async Task<int> InsertBatchAsync<T>(IEnumerable<T> items)
        {
            ArgumentAssert.NotNullOrEmpty(items);

            var type = typeof(T);
            var (sql, parameters) = GenerateInsertInfo(type);

            using var transaction = _connection.BeginTransaction();
            try
            {
                var command = _connection.CreateCommand();
                command.CommandText = sql;
                //AddParametersToCommand(command, parameters);

                foreach (var item in items)
                {
                    UpdateCommandParameters(command, item, parameters);
                    await command.ExecuteNonQueryAsync();
                }
                transaction.Commit();

                return items.Count();
            }
            catch
            {
                transaction.Rollback();
                throw;
            }
            finally
            {
                transaction.Dispose();
            }

        }


        public async Task<int> UpdateAsync<T>(T item)
        {
            ArgumentAssert.NotNull(item);

            var type = typeof(T);
            var (sql, parameters) = GenerateUpdateInfo(type);
            var command = _connection.CreateCommand();
            command.CommandText = sql;
            //AddParametersToCommand(command, parameters);
            UpdateCommandParameters(command, item, parameters);
            return await command.ExecuteNonQueryAsync();

        }



        public async Task<int> AddColumnAsync(string tableName, string cloumnName, string dbType)
        {
            ArgumentAssert.NotNullOrEmpty(tableName);
            ArgumentAssert.NotNullOrEmpty(cloumnName);
            ArgumentAssert.NotNullOrEmpty(dbType);

            var sql = $"ALTER TABLE `{tableName}` ADD COLUMN `{cloumnName}` {dbType}";
            return await ExecuteNonQueryAsync(sql);

        }

        public SqliteTransaction BeginTransaction()
        {
            return _connection.BeginTransaction();
        }
        public SqliteConnection Connection => _connection;

        public object SqlVToValue(Type fi, object s)
        {
            if (fi == typeof(int))
            {
                if (!(s is DBNull))
                {
                    return Convert.ToInt32(s);
                }
                else
                {
                    return 0;
                }
            }
            else if (fi == typeof(decimal))
            {
                if (!(s is DBNull))
                {
                    return Convert.ToDecimal(s);
                }
                else
                {
                    return 0;
                }
            }
            else if (fi == typeof(double))
            {
                if (!(s is DBNull))
                {
                    return Convert.ToDouble(s);
                }

                else
                {
                    return 0;
                }
            }
            else if (fi == typeof(bool))
            {
                if (!(s is DBNull))
                {
                    return Convert.ToInt32(s) == 1;
                }

                else
                {
                    return false;
                }
            }
            else if (fi == typeof(string))
            {
                if (!(s is DBNull))
                {
                    if (s is DateTime)
                    {
                        return ((DateTime)s).ToString("yyyy/MM/dd HH:mm:ss.fff");
                    }
                    return Convert.ToString(s);
                }
                else
                {
                    return "";
                }
            }
            else if (fi == typeof(DateTime))
            {
                if (!(s is DBNull))
                {
                    Type p = s.GetType();
                    if (s is double)
                    {
                        return DateTime.FromOADate((double)s);
                    }
                    else if (s is Single)
                    {
                        return DateTime.FromOADate((double)s);
                    }
                    else if (s is decimal)
                    {
                        return DateTime.FromOADate(Convert.ToDouble(s));
                    }
                    else
                    {
                        return Convert.ToDateTime(s);
                    }


                }
                else
                {
                    return null;
                }

            }
            else if (fi == typeof(bool))
            {
                if (!(s is DBNull))
                {
                    return Convert.ToBoolean(s);
                }
                else
                {
                    return null;
                }
            }
            else if (fi == typeof(long))
            {
                if (!(s is DBNull))
                {
                    return Convert.ToInt64(s);
                }
                else
                {
                    return 0;
                }

            }
            else
            {
                return null;
            }

        }

        // 释放资源
        public void Dispose()
        {
            // 优化关闭前的数据库整理
            if (_connection != null && _connection.State == ConnectionState.Open)
            {
                using var cmd = new SqliteCommand("PRAGMA optimize", _connection);
                cmd.ExecuteNonQuery();
                _connection.Close();
            }
            _connection?.Dispose();
            GC.SuppressFinalize(this);
        }

        #region Helper Methods
        private void AddParameters(SqliteCommand command, Dictionary<string, object> parameters)
        {
            if (parameters == null) return;

            foreach (var param in parameters)
            {
                command.Parameters.AddWithValue($"@{param.Key}", param.Value ?? DBNull.Value);
            }
        }

        private (string sql, List<string> parameters) GenerateInsertInfo(Type type)
        {
            var properties = type.GetProperties();
            var columns = new List<string>();
            var values = new List<string>();
            var parameters = new List<string>();

            foreach (var prop in properties)
            {
                // 排除只读属性
                if (prop.GetSetMethod() == null) continue;

                columns.Add($"[{prop.Name}]");
                values.Add($"@{prop.Name}");
                parameters.Add(prop.Name);
            }
            var tableName = type.Name + "s"; // 默认使用类名作为表名
            var tableNameAttribute = type.GetCustomAttribute<TableNameAttribute>();
            if (tableNameAttribute != null)
            {
                tableName = tableNameAttribute.TableName; // 使用自定义表名
            }
            var sql = $"INSERT INTO {tableName} ({string.Join(", ", columns)}) " +
                      $"VALUES ({string.Join(", ", values)})";

            return (sql, parameters);
        }

        private (string sql, List<string> parameters) GenerateUpdateInfo(Type type)
        {
            var properties = type.GetProperties();
            var columns = new List<string>();
            var parameters = new List<string>();

            foreach (var prop in properties)
            {
                // 排除只读属性
                if (prop.GetSetMethod() == null) continue;
                if (prop.Name.ToLower() != "id")
                {
                    columns.Add($"[{prop.Name}]=@{prop.Name}");
                }
                parameters.Add(prop.Name);
            }
            var tableName = type.Name + "s"; // 默认使用类名作为表名
            var tableNameAttribute = type.GetCustomAttribute<TableNameAttribute>();
            if (tableNameAttribute != null)
            {
                tableName = tableNameAttribute.TableName; // 使用自定义表名
            }
            var sql = $"Update {tableName} ({string.Join(", ", columns)}) " +
                      $"WHERE Id=@Id";

            return (sql, parameters);
        }

        private List<T> MapReaderToObjects<T>(SqliteDataReader reader) where T : new()
        {
            var results = new List<T>();
            var properties = typeof(T).GetProperties().Where(p => p.CanWrite);

            while (reader.Read())
            {
                var item = new T();
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    var colName = reader.GetName(i);
                    var prop = properties.FirstOrDefault(p =>
                        p.Name.Equals(colName, StringComparison.OrdinalIgnoreCase));

                    if (prop != null && !reader.IsDBNull(i))
                    {
                        var value = reader.GetValue(i);

                        SetPropertyValue(reader.GetFieldType(i), value, item, prop);

                        prop.SetValue(item, value);
                    }
                }
                results.Add(item);
            }
            return results;
        }

        private void SetPropertyValue(Type typeFromDb, object value, object obj, PropertyInfo prop)
        {
            if (prop.PropertyType == typeof(DateTime) && typeFromDb == typeof(string))
            {
                if (DateTime.TryParse(value.ToString(), out DateTime dt))
                {
                    prop.SetValue(obj, dt);
                    return;
                }
            }
            prop.SetValue(obj, value);


        }

        private List<Dictionary<string, object>> MapReaderToDictionaryList(SqliteDataReader reader)
        {
            var results = new List<Dictionary<string, object>>();

            while (reader.Read())
            {
                var item = new Dictionary<string, object>();
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    var colName = reader.GetName(i);
                    if (reader.IsDBNull(i))
                    {
                        item[colName] = null;
                    }
                    else
                    {
                        item[colName] = reader.GetValue(i);
                    }

                }
                results.Add(item);
            }
            return results;
        }

        private T MapReaderToObject<T>(SqliteDataReader reader) where T : new()
        {
            var item = new T();
            var properties = typeof(T).GetProperties().Where(p => p.CanWrite);

            if (reader.Read())
            {
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    var colName = reader.GetName(i);
                    var prop = properties.FirstOrDefault(p =>
                        p.Name.Equals(colName, StringComparison.OrdinalIgnoreCase));

                    if (prop != null && !reader.IsDBNull(i))
                    {
                        var value = reader.GetValue(i);
                        prop.SetValue(item, value);
                    }
                }
            }
            return item;
        }

        private Dictionary<string, object> MapReaderToDictionary(SqliteDataReader reader)
        {
            var results = new Dictionary<string, object>();

            if (reader.Read())
            {
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    var colName = reader.GetName(i);
                    if (reader.IsDBNull(i))
                    {
                        results[colName] = null;
                    }
                    else
                    {
                        results[colName] = reader.GetValue(i);
                    }

                }
            }
            return results;
        }

        //private void AddParametersToCommand(SqliteCommand command, object item, List<string> paramNames)
        //{
        //    command.Parameters.Clear();
        //    var type = item.GetType();
        //    foreach (var name in paramNames)
        //    {
        //        var value = type.GetProperty(name)?.GetValue(item) ?? DBNull.Value;
        //        SqliteParameter sqliteParameter = new SqliteParameter("@" + name, value);
        //        command.Parameters.Add(sqliteParameter);
        //    }
        //}

        private void UpdateCommandParameters(SqliteCommand command, object item, List<string> paramNames)
        {
            command.Parameters.Clear();
            var type = item.GetType();
            foreach (var param in paramNames)
            {
                var property = type.GetProperty(param);
                object value = property?.GetValue(item);
                if (property == null || value == null)
                {
                    value = DBNull.Value;
                }
                else
                {
                    if (property.PropertyType == typeof(DateTime?))
                    {
                        value = ((DateTime?)value)?.ToString("yyyy-MM-dd mm:hh:ss");
                    }
                }

                SqliteParameter sqliteParameter = new SqliteParameter("@" + param, value);
                command.Parameters.Add(sqliteParameter);
            }
        }
        #endregion
    }
}
